﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// 数据库操作类
/// </summary>
internal class CDatabase : IDisposable
{
    /// <summary>
    /// 数据库连接对象
    /// </summary>
    public SqlConnection Connection;
    public int num_rows;
    public SortedList row;
    public DataRowCollection rows;

    public CDatabase()
    {
    }

    /// <summary>
    /// 构造函数
    /// </summary>
    /// <param name="db_host"></param>
    /// <param name="db_user"></param>
    /// <param name="db_password"></param>
    /// <param name="db_name"></param>
    public CDatabase(string db_host, string db_user, string db_password, string db_name)
    {
        this.open("Data Source=" + db_host + ";User Id=" + db_user + ";Password=" + db_password + ";Initial Catalog=" + db_name + ";Persist Security Info=no;Integrated Security=no;");
    }

    /// <summary>
    /// 清理连接池
    /// </summary>
    public static void ClarePools()
    {
        SqlConnection.ClearAllPools();
    }

    /// <summary>
    /// 关闭数据库连接
    /// </summary>
    public void close()
    {
        if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
        {
            this.Connection.Close();
            this.Connection.Dispose();
        }
    }

    public void Dispose()
    {
        this.close();
    }

    /// <summary>
    /// 执行存储过程
    /// </summary>
    /// <param name="ProcedureName"> 过程名 </param>
    public void execute_procedure(string ProcedureName)
    {
        if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
        {
            try
            {
                SqlCommand command = new SqlCommand(ProcedureName, this.Connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Clear();
                command.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
                command.ExecuteNonQuery();
                command.Dispose();
            }
            catch (SqlException exception)
            {
                Console.WriteLine(exception.Message);
            }
        }
    }

    /// <summary>
    /// 执行存储过程
    /// </summary>
    /// <param name="ProcedureName"> 过程名 </param>
    /// <param name="parameters"> 泛型参数集 </param>
    public void execute_procedure(string ProcedureName, List<SqlParameter> parameters)
    {
        if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
        {
            try
            {
                SqlCommand command = new SqlCommand(ProcedureName, this.Connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Clear();
                if (parameters != null)
                {
                    foreach (SqlParameter parameter in parameters)
                    {
                        command.Parameters.Add(parameter);
                    }
                }
                command.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
                command.ExecuteNonQuery();
                command.Dispose();
            }
            catch (SqlException exception)
            {
                Console.WriteLine(exception.Message);
            }
        }
    }

    /// <summary>
    /// 执行存储过程
    /// </summary>
    /// <param name="ProcedureName"> 过程名 </param>
    /// <param name="parameters"> 参数集 </param>
    public void execute_procedure(string ProcedureName, SqlParameter[] parameters)
    {
        if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
        {
            try
            {
                SqlCommand command = new SqlCommand(ProcedureName, this.Connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Clear();
                if (parameters != null)
                {
                    foreach (SqlParameter parameter in parameters)
                    {
                        command.Parameters.Add(parameter);
                    }
                }
                command.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
                command.ExecuteNonQuery();
                command.Dispose();
            }
            catch (SqlException exception)
            {
                Console.WriteLine(exception.Message);
            }
        }
    }

    /// <summary>
    /// 执行存储过程
    /// </summary>
    /// <param name="ProcedureName"> 过程名 </param>
    /// <param name="parameters"> 参数集 </param>
    /// <param name="errormsg"> 错误信息 </param>
    /// <returns></returns>
    public bool execute_procedure_stone(string ProcedureName, SqlParameter[] parameters, ref string errormsg)
    {
        bool flag = true;
        if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
        {
            try
            {
                SqlCommand command = new SqlCommand(ProcedureName, this.Connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Clear();
                if (parameters != null)
                {
                    foreach (SqlParameter parameter in parameters)
                    {
                        command.Parameters.Add(parameter);
                    }
                }
                command.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
                command.ExecuteNonQuery();
                command.Dispose();
            }
            catch (SqlException exception)
            {
                errormsg = exception.Message;
                flag = false;
            }
        }
        return flag;
    }

    /// <summary>
    /// 执行存储过程，带返回值
    /// </summary>
    /// <param name="ProcedureName"> 过程名 </param>
    public void fetch_procedure(string ProcedureName)
    {
        if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
        {
            this.num_rows = 0;
            try
            {               
                SqlCommand selectCommand = new SqlCommand(ProcedureName, this.Connection);
                selectCommand.CommandType = CommandType.StoredProcedure;
                selectCommand.Parameters.Clear();
                selectCommand.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
                SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);                
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);
                if (dataSet.Tables.Count == 1)
                {
                    this.rows = dataSet.Tables[0].Rows;
                    this.num_rows = this.rows.Count;
                }
                else
                {
                    this.rows = null;
                    this.num_rows = 0;
                }
                dataSet.Dispose();
                adapter.Dispose();
                selectCommand.Dispose();
            }
            catch (SqlException exception)
            {
                Console.WriteLine(exception.Message);
            }
        }
    }

    /// <summary>
    /// 执行存储过程，带返回值
    /// </summary>
    /// <param name="ProcedureName"> 过程名 </param>
    /// <param name="parameters"> 泛型参数集 </param>
    public void fetch_procedure(string ProcedureName, List<SqlParameter> parameters)
    {
        if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
        {
            this.num_rows = 0;
            try
            {
                SqlCommand selectCommand = new SqlCommand(ProcedureName, this.Connection);
                selectCommand.CommandType = CommandType.StoredProcedure;
                if (parameters != null)
                {
                    foreach (SqlParameter parameter in parameters)
                    {
                        selectCommand.Parameters.Add(parameter);
                    }
                }
                SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);
                if (dataSet.Tables.Count == 1)
                {
                    this.rows = dataSet.Tables[0].Rows;
                    this.num_rows = this.rows.Count;
                }
                else
                {
                    this.num_rows = 0;
                }
                dataSet.Dispose();
                adapter.Dispose();
                selectCommand.Dispose();
            }
            catch
            {
            }
        }
    }

    /// <summary>
    /// 执行存储过程，带返回值
    /// </summary>
    /// <param name="ProcedureName"> 过程名 </param>
    /// <param name="parameters"> 泛型参数集 </param>
    public void fetch_procedure(string ProcedureName, SqlParameter[] parameters)
    {
        if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
        {
            this.num_rows = 0;
            try
            {
                SqlCommand selectCommand = new SqlCommand(ProcedureName, this.Connection);
                selectCommand.CommandType = CommandType.StoredProcedure;
                selectCommand.Parameters.Clear();
                if (parameters != null)
                {
                    foreach (SqlParameter parameter in parameters)
                    {
                        selectCommand.Parameters.Add(parameter);
                    }
                }
                selectCommand.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
                SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);
                if (dataSet.Tables.Count == 1)
                {
                    this.rows = dataSet.Tables[0].Rows;
                    this.num_rows = this.rows.Count;
                }
                else
                {
                    this.rows = null;
                    this.num_rows = 0;
                }
                dataSet.Dispose();
                adapter.Dispose();
                selectCommand.Dispose();
            }
            catch (SqlException exception)
            {
                Console.WriteLine(exception.Message);
            }
        }
    }

    /// <summary>
    /// 执行存储过程，返回DataSet
    /// </summary>
    /// <param name="ProcedureName"></param>
    /// <returns></returns>
    public DataSet GetDataSet(string ProcedureName)
    {
        DataSet dataSet = new DataSet();
        if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
        {
            try
            {
                SqlCommand selectCommand = new SqlCommand(ProcedureName, this.Connection);
                selectCommand.CommandType = CommandType.StoredProcedure;
                selectCommand.Parameters.Clear();
                selectCommand.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
                SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
                adapter.Fill(dataSet);
                adapter.Dispose();
                selectCommand.Dispose();
            }
            catch (SqlException exception)
            {
                Console.WriteLine(exception.Message);
            }
        }
        return dataSet;
    }

    /// <summary>
    /// 执行存储过程，返回DataSet
    /// </summary>
    /// <param name="ProcedureName"> 过程名 </param>
    /// <param name="parameters"> 参数集 </param>
    /// <returns></returns>
    public DataSet GetDataSet(string ProcedureName, SqlParameter[] parameters)
    {
        DataSet dataSet = new DataSet();
        if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
        {
            try
            {
                SqlCommand selectCommand = new SqlCommand(ProcedureName, this.Connection);
                selectCommand.CommandType = CommandType.StoredProcedure;
                selectCommand.Parameters.Clear();
                if (parameters != null)
                {
                    foreach (SqlParameter parameter in parameters)
                    {
                        selectCommand.Parameters.Add(parameter);
                    }
                }
                selectCommand.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
                SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
                adapter.Fill(dataSet);
                adapter.Dispose();
                selectCommand.Dispose();
            }
            catch (SqlException exception)
            {
                Console.WriteLine(exception.Message);
            }
        }
        return dataSet;
    }

    /// <summary>
    /// 执行存储过程，返回DataTable
    /// </summary>
    /// <param name="ProcedureName"> 过程名 </param>
    /// <returns></returns>
    public DataTable GetDataTable(string ProcedureName)
    {
        DataTable table = null;
        if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
        {
            this.num_rows = 0;
            try
            {
                SqlCommand selectCommand = new SqlCommand(ProcedureName, this.Connection);
                selectCommand.CommandType = CommandType.StoredProcedure;
                selectCommand.Parameters.Clear();
                selectCommand.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
                SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);
                if (dataSet.Tables.Count == 1)
                {
                    table = dataSet.Tables[0];
                }
                dataSet.Dispose();
                adapter.Dispose();
                selectCommand.Dispose();
            }
            catch (SqlException exception)
            {
                Console.WriteLine(exception.Message);
            }
        }
        return table;
    }

    /// <summary>
    /// 执行存储过程，返回DataTable
    /// </summary>
    /// <param name="ProcedureName"> 过程名 </param>
    /// <param name="parameters"> 参数集 </param>
    /// <returns></returns>
    public DataTable GetDataTable(string ProcedureName, SqlParameter[] parameters)
    {
        DataTable table = null;
        if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
        {
            this.num_rows = 0;
            try
            {
                SqlCommand selectCommand = new SqlCommand(ProcedureName, this.Connection);
                selectCommand.CommandType = CommandType.StoredProcedure;
                selectCommand.Parameters.Clear();
                if (parameters != null)
                {
                    foreach (SqlParameter parameter in parameters)
                    {
                        selectCommand.Parameters.Add(parameter);
                    }
                }
                selectCommand.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
                SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);
                if (dataSet.Tables.Count == 1)
                {
                    table = dataSet.Tables[0];
                }
                dataSet.Dispose();
                adapter.Dispose();
                selectCommand.Dispose();
            }
            catch (SqlException exception)
            {
                Console.WriteLine(exception.Message);
            }
        }
        return table;
    }

    /// <summary>
    /// 执行存储过程，返回DataTable
    /// </summary>
    /// <param name="ProcedureName"></param>
    /// <param name="parameters"></param>
    /// <returns></returns>
    public DataTable GetDataTable(string ProcedureName, List<SqlParameter> parameters)
    {
        DataTable table = new DataTable();
        if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
        {
            this.num_rows = 0;
            try
            {
                SqlCommand selectCommand = new SqlCommand(ProcedureName, this.Connection);
                selectCommand.CommandType = CommandType.StoredProcedure;
                selectCommand.Parameters.Clear();
                if (parameters != null)
                {
                    foreach (SqlParameter parameter in parameters)
                    {
                        selectCommand.Parameters.Add(parameter);
                    }
                }
                selectCommand.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
                SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
                adapter.Fill(table);

                adapter.Dispose();
                selectCommand.Dispose();
            }
            catch (SqlException exception)
            {
                Console.WriteLine(exception.Message);
            }
        }
        return table;
    }

    /// <summary>
    /// 打开连接
    /// </summary>
    /// <param name="ConnectionString"></param>
    private void open(string ConnectionString)
    {
        this.num_rows = 0;
        if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
        {
            this.Connection.Close();
        }
        try
        {
            this.Connection = new SqlConnection(ConnectionString);
            this.Connection.Open();
            this.row = new SortedList();
        }
        catch (SqlException exception)
        {
            Console.WriteLine(exception.Message);
        }
    }

    /// <summary>
    /// 打开连接,返回是否成功
    /// </summary>
    /// <param name="ConnectionString"></param>
    /// <returns></returns>
    public bool isConnection(string ConnectionString)
    {
        this.num_rows = 0;
        if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
        {
            this.Connection.Close();
        }
        try
        {
            this.Connection = new SqlConnection(ConnectionString);
            this.Connection.Open();
            this.row = new SortedList();
            return true;
        }
        catch (SqlException exception)
        {
            Console.WriteLine(exception.Message);
        }
        return false;
    }

    /// <summary>
    /// 打开连接,返回是否成功
    /// </summary>
    /// <param name="ConnectionString"></param>
    /// <returns></returns>
    public bool isConnection(string db_host, string db_user, string db_password, string db_name)
    {
        string ConnectionString = "Data Source=" + db_host + ";User Id=" + db_user + ";Password=" + db_password + ";Initial Catalog=" + db_name + ";Persist Security Info=no;Integrated Security=no;";
        
        return isConnection(ConnectionString);
    }

    /// <summary>
    /// 获取该库下所有数据表名称
    /// </summary>
    /// <returns></returns>
    public string GetTableName()
    {
        string strTable = "";

        if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
        {
            //使用信息架构视图
            //select name from sys.all_objects where type = 'U' 
            SqlCommand sqlcmd = new SqlCommand("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'", Connection);
            SqlDataReader dr = sqlcmd.ExecuteReader();

            while (dr.Read())
            {
                strTable += dr.GetString(0) + ",";
            }
        }

        return strTable;
    }

    /// <summary>
    /// 执行sql返回结果集
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public DataTable ExecuteSql(string sql)
    {
        DataTable dt = null;
        if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
        {                              
            SqlDataAdapter sqlda = new SqlDataAdapter(sql, Connection);

            try
            {
                DataSet ds = new DataSet();
                sqlda.Fill(ds);
                if (ds.Tables.Count == 1)
                {
                    dt = ds.Tables[0];
                }

                ds.Dispose();
                sqlda.Dispose();                
            }
            catch (SqlException exception)
            {
                Console.WriteLine(exception.Message);
            }
        }

        return dt;
    }

}

